import pandas as pd
import numpy as np
import os
import datetime
from decimal import *
import Core.Gadget as Gadget
from dateutil.relativedelta import relativedelta


def Fill_ReleaseDate(df, lag_release_month=1, release_day=1):
    #
    def AddReleaseDate(row, lag_release_month, release_day, datetime_field):
        report_date = row[datetime_field]
        cur_month = report_date.month
        #
        if cur_month == 12:
            year = report_date.year + 1
            month = 1
        else:
            year = report_date.year
            month = report_date.month + lag_release_month
        #
        release_date = datetime.datetime(year, month, release_day)
        return release_date
    #
    df["Release_Date"] = df.apply(lambda x: AddReleaseDate(x, lag_release_month, release_day, "Report_Date"), axis=1)
    # print(df)


def Query_Data(database,code,datetime1,datetime2):
    # 以下语句相当于
    # Select * From factor.cap where symbol = "000001.SZ" and date >= 20200101 and date <= 20200201 order by date desc

    # 构造筛选条件
    filter = []
    filter.append(["name", code])
    filter.append(["report_date", ">=", datetime1])
    filter.append(["report_date", "<=", datetime2])

    # 构造排序条件
    sort = [("date", 1)] # 因为可以多列排序，所以采用的是List结构，-1是降序

    #
    documents = database.Find(databaseName="factor",  # 库名
                              tableName="a_sys_factor_raw",  # 表名
                              filter=filter,  # 筛选条件
                              sort=sort)  # 排序
    documents= Gadget.DocumentsToDataFrame(documents)
    documents=documents[['report_date','value']]
    documents.set_index('report_date',inplace=True)
    documents.columns=[code]
    return documents
    #for item in documents:
    #    print(item)


def Query_Data_h11001(database,code,datetime1,datetime2):
    # 以下语句相当于
    # Select * From factor.cap where symbol = "000001.SZ" and date >= 20200101 and date <= 20200201 order by date desc

    # 构造筛选条件
    filter = []
    filter.append(["symbol", code])
    filter.append(["date", ">=", datetime1])
    filter.append(["date", "<=", datetime2])

    # 构造排序条件
    sort = [("date", 1)] # 因为可以多列排序，所以采用的是List结构，-1是降序

    #
    documents = database.Find(databaseName="financial_data",  # 库名
                              tableName="index_dailybar",  # 表名
                              filter=filter,  # 筛选条件
                              sort=sort)  # 排序
    documents= Gadget.DocumentsToDataFrame(documents)
    documents=documents[['date','close']]
    documents.set_index('date',inplace=True)
    documents.columns=[code]
    return documents



def h11001(database,datetime1,datetime2):
    #
    df1 = Query_Data_h11001(database,'H11001.CSI',datetime1,datetime2)
    df1['yearmonth'] = df1.index.map(lambda x :100*x.year+x.month)
    df1 = df1.groupby('yearmonth').last()
    df1 = np.log(df1)
    df1['factor'] = df1.diff()
    df1['Report_Date'] = df1.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df1['Release_Date'] = df1['Report_Date']
    print(df1)
    #return(df1,'H11001_Monthly_Return')
    #Sava_Systmetic_Factor_To_Database(database, df1, savedName='H11001_Monthly_Return', fieldName='factor')


def omo(database):

    df = Query_Data(database, 'M0061614')
    print(df)

def slfmlfpsl(database,datetime1,datetime2):

    df1 = Query_Data(database,'M5462036',datetime1,datetime2)
    df2 = Query_Data(database,'M5528822',datetime1,datetime2)
    df3 = Query_Data(database,'M5515072',datetime1,datetime2)
    df2 = pd.concat([df1, df2, df3],axis=1)
    df2['sum'] = df2.sum(axis=1)
    df2['factor'] = df2['sum'].diff()
    df2['Report_Date'] = df2.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df2, lag_release_month=1, release_day=6)
    print(df2)
    #return(df2,'SLF_MLF_PSL')
    #Sava_Systmetic_Factor_To_Database(database, df2, savedName='SLF_MLF_PSL', fieldName='factor')


def govt_leverage(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0001707',datetime1,datetime2)
    df2 = Query_Data(database,'M0043411',datetime1,datetime2)
    df3 = pd.concat([df1,df2],axis=1)
    df3['div'] = df3['M0001707'] / df3['M0043411']
    df3['log'] = np.log(df3['div'])
    df3['factor'] = df3['log'].diff()
    df3['Report_Date'] = df3.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df3, lag_release_month=1, release_day=14)
    print(df3)
    #return(df3,'Govt_Leverage_Dif')
    Sava_Systmetic_Factor_To_Database(database, df3, savedName='Govt_Leverage_Dif', fieldName='factor')


def difln_fxreserve(database,datetime1,datetime2):

    df4 = Query_Data(database,'M0010049',datetime1,datetime2)
    df4['log'] = np.log(df4['M0010049'])
    df4['factor'] = df4['log'].diff()
    df4['Report_Date'] = df4.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df4, lag_release_month=1, release_day=9)
    print(df4)
    #return(df4,'FX_Reserve_Dif')
    Sava_Systmetic_Factor_To_Database(database, df4, savedName='FX_Reserve_Dif', fieldName='factor')


def dif_hotmoney(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0010049',datetime1,datetime2)
    df2 = Query_Data(database,'M0009870',datetime1,datetime2)
    df3 = Query_Data(database,'M5200002',datetime1,datetime2)
    df4 = Query_Data(database,'M5207849',datetime1,datetime2)
    df5 = pd.concat([df1,df2,df3,df4],axis=1)
    df5.fillna(0,inplace=True)    #这个因子全部用0填充了
    df5['diff'] = df5['M0010049'].diff()
    df5['sub'] = df5['M0009870'] - df5['M5200002']
    df5['factor'] = df5['diff'] - df5['sub']- df5['M5207849']
    df5['Report_Date'] = df5.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df5, lag_release_month=1, release_day=20)
    #df5['diff2']=df5['sub2'].diff()
    print(df5)
    #return(df5,'Hot_Money_Dif')
    Sava_Systmetic_Factor_To_Database(database, df5, savedName='Hot_Money_Dif', fieldName='factor')


def rd(database,datetime1,datetime2):

    df6 = Query_Data(database,'M0061518',datetime1,datetime2)
    df6['factor'] = df6.diff()
    df6['Report_Date'] = df6.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df6, lag_release_month=1, release_day=1)
    print(df6)
    #return(df6,'Deposit_Reserve_Ratio_Dif')
    Sava_Systmetic_Factor_To_Database(database, df6, savedName='Deposit_Reserve_Ratio_Dif', fieldName='factor')


def dif_rdd(database,datetime1,datetime2):

    df7 = Query_Data(database,'M0010096',datetime1,datetime2)
    df7['factor'] = df7.diff()
    df7['Report_Date'] = df7.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df7['Release_Date'] = df7['Report_Date']
    print(df7)
    #return(df7,'Excess_Reserves_Ratio_Dif')
    Sava_Systmetic_Factor_To_Database(database, df7, savedName='Excess_Reserves_Ratio_Dif', fieldName='factor')


def lqdtysurplus(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0001385',datetime1,datetime2)
    df2 = Query_Data(database,'M0000612',datetime1,datetime2)
    df3 = Query_Data(database,'M0000545',datetime1,datetime2)
    df8 = pd.concat([df1,df2,df3],axis=1)
    df8['factor'] = df8['M0001385'] - df8['M0000612'] - df8['M0000545']
    df8['Report_Date'] = df8.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df8, lag_release_month=1, release_day=15)
    print(df8)
    #return(df8,'Liquidity_Surplus')
    Sava_Systmetic_Factor_To_Database(database, df8, savedName='Liquidity_Surplus', fieldName='factor')


def banksurplus(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0009969',datetime1,datetime2)
    df2 = Query_Data(database,'M0009978',datetime1,datetime2)
    df3 = Query_Data(database,'M0000545',datetime1,datetime2)
    df4 = Query_Data(database,'M0000273',datetime1,datetime2)
    df9 = pd.concat([df1,df2,df3,df4],axis=1)
    df9['proportion'] = df9['M0009969'] / (df9['M0009969'] + df9['M0009978'])
    df9['growth'] = (df9['M0000545'] + df9['M0000273']) / 100
    df9['factor'] = 1 - df9['proportion'] - df9['growth']
    df9['Report_Date'] = df9.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df9, lag_release_month=1, release_day=15)
    print(df9)
    #return(df9,'Bank_Surplus')
    Sava_Systmetic_Factor_To_Database(database, df9, savedName='Bank_Surplus', fieldName='factor')


def save_loan(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0009969',datetime1,datetime2)
    df2 = Query_Data(database,'M0009978',datetime1,datetime2)
    df10 = pd.concat([df1,df2],axis=1)
    df10['div'] = df10['M0009969'] / (df10['M0009969'] + df10['M0009978'])
    df10['factor'] = df10['div'].diff()
    df10['Report_Date'] = df10.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df10, lag_release_month=1, release_day=15)
    print(df10)
    #return(df10,'Deposit_Loan_Ratio_Diff')
    Sava_Systmetic_Factor_To_Database(database, df10, savedName='Deposit_Loan_Ratio_Diff', fieldName='factor')


def tedsprd(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0017142',datetime1,datetime2)
    df2 = Query_Data(database,'S0059741',datetime1,datetime2)
    df11 = pd.concat([df1,df2],axis=1)
    df11['yearmonth'] = df11.index.map(lambda x: 100*x.year + x.month)
    df11 = df11.groupby('yearmonth').mean()
    df11['sub'] = df11['M0017142']-df11['S0059741']
    df11['factor'] = df11['sub'].diff()
    df11['Report_Date'] = df11.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df11['Release_Date'] = df11['Report_Date']
    print(df11)
    #return(df11,'Ted_Spread')
    Sava_Systmetic_Factor_To_Database(database, df11, savedName='Ted_Spread', fieldName='factor')


def difin_invstablecpt(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0009978',datetime1,datetime2)
    df2 = Query_Data(database,'M0001693',datetime1,datetime2)
    df12 = pd.concat([df1,df2],axis=1)
    df12['sum'] = df12.sum(axis=1)
    df12['log'] = np.log(df12['sum'])
    df12['factor'] = df12['log'].diff()
    df12['Report_Date'] = df12.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df12, lag_release_month=1, release_day=15)
    print(df12)
    #return(df12,'Invstable_CPT_Dif')
    Sava_Systmetic_Factor_To_Database(database, df12, savedName='Invstable_CPT_Dif', fieldName='factor')


def dif_indav(database,datetime1,datetime2):

    df15 = Query_Data(database,'M0000545',datetime1,datetime2)
    df15['factor'] = df15.diff()
    df15['Report_Date'] = df15.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df15, lag_release_month=1, release_day=15)
    print(df15)
    #return(df15,'Industrial_Value_Added_YoY')
    Sava_Systmetic_Factor_To_Database(database, df15, savedName='H11001_Monthly_Return', fieldName='factor')


def dif_realestate(database,datetime1,datetime2):

    df16 = Query_Data(database,'S0029657',datetime1,datetime2)
    df16['factor'] = df16.diff()
    df16['Report_Date'] = df16.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df16, lag_release_month=1, release_day=18)
    print(df16)
    #return(df16,'RE_Invested_YoY_Dif')
    Sava_Systmetic_Factor_To_Database(database, df16, savedName='RE_Invested_YoY_Dif', fieldName='factor')


def accfixinvs(database,datetime1,datetime2):

    df17 = Query_Data(database,'M0000273',datetime1,datetime2)
    df17['factor'] = df17.diff()
    df17['Report_Date'] = df17.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df17, lag_release_month=1, release_day=15)
    print(df17)
    #return(df17,'Fixed_Asset_Invested_Aggr_YoY')
    Sava_Systmetic_Factor_To_Database(database, df17, savedName='Fixed_Asset_Invested_Aggr_YoY', fieldName='factor')


def cpimppi(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0000613',datetime1,datetime2)
    df2 = Query_Data(database,'M0001227',datetime1,datetime2)
    df18 = pd.concat([df1,df2],axis=1)
    df18['factor'] = df18['M0000613'] - df18['M0001227']
    df18['Report_Date'] = df18.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df18, lag_release_month=1, release_day=12)
    print(df18)
    #return(df18,'CPI_PPI_Spread_NoFood')
    Sava_Systmetic_Factor_To_Database(database, df18, savedName='CPI_PPI_Spread_NoFood', fieldName='factor')


def difln_usdchn(database,datetime1,datetime2):
   #这个是日数据 取月末数

    df19 = Query_Data(database,'M0000185',datetime1,datetime2)
    df19['yearmonth'] = df19.index.map(lambda x:100*x.year+x.month)
    df19 = df19.groupby('yearmonth').last()
    df19['log'] = np.log(df19)
    df19['factor'] = df19['log'].diff()
    df19['Report_Date'] = df19.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df19['Release_Date'] = df19['Report_Date']
    print(df19)
    #return(df19,'USDCNY_PBOC_Montyly_Return')
    Sava_Systmetic_Factor_To_Database(database, df19, savedName='USDCNY_PBOC_Montyly_Return', fieldName='factor')


def exim(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0000610',datetime1,datetime2)
    df2 = Query_Data(database, 'M0000604',datetime1,datetime2)
    df20 = pd.concat([df1,df2],axis=1)
    df20['factor'] = df20['M0000610'] / df20['M0000604']
    df20['Report_Date'] = df20.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df20, lag_release_month=1, release_day=23)
    print(df20)
    #return(df20,'Net_Export_Ratio')
    Sava_Systmetic_Factor_To_Database(database, df20, savedName='Net_Export_Ratio', fieldName='factor')


def dif_liability(database,datetime1,datetime2):

    df21 = Query_Data(database,'M0044700',datetime1,datetime2)
    df21['factor'] = df21.diff()/100
    df21['Report_Date'] = df21.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df21, lag_release_month=1, release_day=28)
    print(df21)
    #return(df21,'Industrial_Liab_Ratio_Dif')
    Sava_Systmetic_Factor_To_Database(database, df21, savedName='Industrial_Liab_Ratio_Dif', fieldName='factor')


def dif_profmargin(database,datetime1,datetime2):

    df22 = Query_Data(database,'M5207655',datetime1,datetime2)
    df22['factor'] = df22.diff()/100
    df22['Report_Date'] = df22.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df22, lag_release_month=1, release_day=28)
    print(df22)
    #return(df22,'Industrial_Profitmargin_Dif')
    Sava_Systmetic_Factor_To_Database(database, df22, savedName='Industrial_Profitmargin_Dif', fieldName='factor')


def dif_att(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0000554',datetime1,datetime2)
    df2 = Query_Data(database,'M0007501',datetime1,datetime2)
    df23 = pd.concat([df1,df2],axis=1)
    df23['M0000554'] = df23['M0000554'].diff()
    df23['M0000554'].fillna(method='ffill',inplace=True)
    df23['div'] = df23['M0007501'] / df23['M0000554']
    df23['factor'] = df23['div'].diff()
    df23['Report_Date'] = df23.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df23['Release_Date'] = df23['Report_Date']
    print(df23)
    #return(df23,'Industrial_ATO_Dif')
    Sava_Systmetic_Factor_To_Database(database, df23, savedName='Industrial_ATO_Dif', fieldName='factor')


def newloan(database,datetime1,datetime2):

    df24 = Query_Data(database,'M0009973',datetime1,datetime2)
    df24['factor'] = df24.diff()
    df24['Report_Date'] = df24.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df24, lag_release_month=1, release_day=10)
    print(df24)
    #return(df24,'NewLoan_Dif')
    Sava_Systmetic_Factor_To_Database(database, df24, savedName='NewLoan_Dif', fieldName='factor')


def newloan_resid_prop(database,datetime1,datetime2):
    df1 = Query_Data(database,'M0009976',datetime1,datetime2)
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)
    df25 = pd.concat([df1,df2],axis=1)
    df25['factor'] = df25['M0009976'] / df25['M0009973']
    df25['Report_Date'] = df25.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df25, lag_release_month=1, release_day=10)
    print(df25)
    #return(df25,'NewLoan_Resid_Ratio')
    Sava_Systmetic_Factor_To_Database(database, df25, savedName='NewLoan_Resid_Ratio', fieldName='factor')


def newloan_shortprop(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0009974',datetime1,datetime2)
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)
    df26 = pd.concat([df1,df2],axis=1)
    df26['factor'] = df26['M0009974'] / df26['M0009973']
    df26['Report_Date'] = df26.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df26, lag_release_month=1, release_day=10)
    print(df26)
    #return(df26,'NewLoan_ST_Ratio')
    Sava_Systmetic_Factor_To_Database(database, df26, savedName='NewLoan_ST_Ratio', fieldName='factor')


def newloan_longprop(database,datetime1,datetime2):

    df1 = Query_Data(database,'M0009975',datetime1,datetime2)
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)
    df27 = pd.concat([df1,df2],axis=1)
    df27['factor'] = df27['M0009975'] / df27['M0009973']
    df27['Report_Date'] = df27.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df27, lag_release_month=1, release_day=10)
    print(df27)
    #return(df27,'NewLoan_LT_Ratio')
    Sava_Systmetic_Factor_To_Database(database, df27, savedName='NewLoan_LT_Ratio', fieldName='factor')


def difln_multiplier(database,datetime1,datetime2):

    df28 = Query_Data(database,'M0010131',datetime1,datetime2)
    df28['log'] = np.log(df28)
    df28['factor'] = df28['log'].diff()
    df28['Report_Date'] = df28.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df28, lag_release_month=1, release_day=13)
    print(df28)
    #return(df28,'Money_Multiplier_Dif')
    Sava_Systmetic_Factor_To_Database(database, df28, savedName='Money_Multiplier_Dif', fieldName='factor')


def m0(database,datetime1,datetime2):

    df29 = Query_Data(database,'M0001381',datetime1,datetime2)
    df29['Report_Date'] = df29.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df29, lag_release_month=1, release_day=10)
    df29.columns=['factor','Report_Date','Release_Date']
    print(df29)
    #return(df29,'M0_YoY')
    Sava_Systmetic_Factor_To_Database(database, df29, savedName='M0_YoY', fieldName='factor')


def m2mm1(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0001385',datetime1,datetime2)
    df2 = Query_Data(database, 'M0001383',datetime1,datetime2)
    df30 = pd.concat([df1, df2], axis=1)
    df30['factor']=df30['M0001385']-df30['M0001383']
    df30['Report_Date'] = df30.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df30, lag_release_month=1, release_day=10)
    print(df30)
    #return(df30,'M1_M2_Spread')
    Sava_Systmetic_Factor_To_Database(database, df30, savedName='M1_M2_Spread', fieldName='factor')


def timespread(database,datetime1,datetime2):

    df1 = Query_Data(database, 'S0059749',datetime1,datetime2)
    df2 = Query_Data(database, 'S0059744',datetime1,datetime2)
    df31 = pd.concat([df1, df2], axis=1)
    df31['yearmonth']=df31.index.map(lambda x: 100*x.year+x.month)
    df31=df31.groupby('yearmonth').mean()
    df31['sub']=df31['S0059749']-df31['S0059744']
    df31['factor']=df31['sub'].diff()
    df31['Report_Date'] = df31.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df31['Release_Date'] = df31['Report_Date']
    print(df31)
    #return(df31,'Term_Spread_10Y1Y_Monthly')
    Sava_Systmetic_Factor_To_Database(database, df31, savedName='Term_Spread_10Y1Y_Monthly', fieldName='factor')


def creditspd10y(database,datetime1,datetime2):

    df1 = Query_Data(database, 'S0059776',datetime1,datetime2)
    df2 = Query_Data(database, 'S0059749',datetime1,datetime2)
    df32 = pd.concat([df1, df2], axis=1)
    df32['yearmonth'] = df32.index.map(lambda x: 100 * x.year + x.month)
    df32 = df32.groupby('yearmonth').mean()
    df32['sub']=df32['S0059776']-df32['S0059749']
    df32['factor']=df32['sub'].diff()
    df32['Report_Date'] = df32.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df32['Release_Date'] = df32['Report_Date']
    print(df32)
    #return(df32,'Credit_Spread_10Y_Monthly')
    Sava_Systmetic_Factor_To_Database(database, df32, savedName='Credit_Spread_10Y_Monthly', fieldName='factor')


def bondleverage(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0041746',datetime1,datetime2)
    df2 = Query_Data(database, 'M0041747',datetime1,datetime2)
    df3 = Query_Data(database, 'M0041753',datetime1,datetime2)
    df33 = pd.concat([df1,df2,df3], axis=1)
    df33['Report_Date'] = df33.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df33, lag_release_month=1, release_day=8)
    df33['M0041746'] = df33['M0041746'].where(df33['M0041746'].notnull(), 1.05 * df33['M0041747'])
    df33['factor'] = df33['M0041746'] / (df33['M0041746'] - df33['M0041753'])
    print(df33)
    #return(df33,'Bond_Market_Leverage')
    Sava_Systmetic_Factor_To_Database(database, df33, savedName='Bond_Market_Leverage', fieldName='factor')


def mmleverage(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0041739',datetime1,datetime2)
    df2 = Query_Data(database, 'M0010096',datetime1,datetime2)
    df3 = Query_Data(database, 'M0009940',datetime1,datetime2)
    df4 = Query_Data(database,'M0001386',datetime1,datetime2)
    df5 = pd.concat([df2,df3,df4],axis=1)
    df5.sort_index(inplace=True)
    df5.fillna(method='ffill',inplace = True)#用前面的值来填充
    #print(df5)
    df1['yearmonth'] = df1.index.map(lambda x:100*x.year+x.month)
    df1 = df1.groupby('yearmonth').mean()
    df5.index = df5.index.map(lambda x:100*x.year+x.month)
    df34 = pd.concat([df1,df5],axis=1)
    df34['factor'] = (df34['M0041739']+df34['M0010096']/100*df34['M0009940']-df34['M0001386'])/(df34['M0010096']/100*df34['M0009940']-df34['M0001386'])
    df34['Report_Date'] = df34.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    Fill_ReleaseDate(df34, lag_release_month=1, release_day=15)
    print(df34)
    #return(df34,'Money_Market_Leverage')
    Sava_Systmetic_Factor_To_Database(database, df34, savedName='Money_Market_Leverage', fieldName='factor')


def bond_stock(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0041742',datetime1,datetime2)
    df2 = Query_Data(database, 'M0041741',datetime1,datetime2)
    df3 = Query_Data(database, 'M0041740',datetime1,datetime2)
    df4 = Query_Data(database, 'M0041739',datetime1,datetime2)
    df5 = Query_Data(database, 'M0020192',datetime1,datetime2)
    df6 = Query_Data(database, 'M0020255',datetime1,datetime2)
    df35 = pd.concat([df1,df2,df3,df4,df5,df6], axis=1)
    df35['yearmonth'] = df35.index.map(lambda x: 100 * x.year + x.month)
    df35 = df35.groupby('yearmonth').mean()
    df35['sum'] = df35['M0041742']+df35['M0041741']+df35['M0041740']+df35['M0041739']
    df35['sum2'] = df35['M0020192']+df35['M0020255']
    df35['factor'] = df35['sum']/df35['sum2']
    df35['Report_Date'] = df35.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df35['Release_Date'] = df35['Report_Date']
    print(df35)
    #return(df35,'Bond_Stock_Rotate')
    Sava_Systmetic_Factor_To_Database(database, df35, savedName='Bond_Stock_Rotate', fieldName='factor')


def bond_future(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0041742',datetime1,datetime2)
    df2 = Query_Data(database, 'M0041741',datetime1,datetime2)
    df3 = Query_Data(database, 'M0041740',datetime1,datetime2)
    df4 = Query_Data(database, 'M0041739',datetime1,datetime2)
    df5 = Query_Data(database, 'M0024559',datetime1,datetime2)
    df36 = pd.concat([df1, df2, df3, df4], axis=1)
    df36['yearmonth'] = df36.index.map(lambda x: 100 * x.year + x.month)
    df5.index = df5.index.map(lambda x:100*x.year+x.month)
    #df5['M0024559'] = df5['M0024559']/30
    df36 = df36.groupby('yearmonth').sum()
    df36 = np.sum(df36,axis=1)
    df36 = pd.concat([df36,df5],axis=1)
    df36.columns = ['sum','M0024559']
    df36['factor'] = df36['sum']/df36['M0024559']
    df36['Report_Date'] = df36.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df36['Release_Date'] = df36['Report_Date']
    print(df36)
    #return(df36,'Bond_Commodity_Rotate')
    Sava_Systmetic_Factor_To_Database(database, df36, savedName='Bond_Commodity_Rotate', fieldName='factor')


def difln_mkt_stkbd(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M0041746',datetime1,datetime2)
    df2 = Query_Data(database, 'M0041747',datetime1,datetime2)
    df3 = Query_Data(database, 'M0010339',datetime1,datetime2)
    df4 =  Query_Data(database,'M0010359',datetime1,datetime2)
    df37 = pd.concat([df1,df2,df3,df4], axis=1)
    df37['Report_Date'] = df37.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df37, lag_release_month=1, release_day=8)
    df37['M0041746'] = df37['M0041746'].where(df37['M0041746'].notnull(), 1.05 * df37['M0041747'])  #05年之前拼接
    df37['sum'] = df37['M0010339'] + df37['M0010359']
    df37['div'] = df37['M0041746'] / df37['sum']
    df37['log'] = np.log(df37['div'])
    df37['factor'] = df37['log'].diff()
    print(df37)
    #return(df37,'Stock_Cap_To_Bond_Dif')
    Sava_Systmetic_Factor_To_Database(database, df37, savedName='Stock_Cap_To_Bond_Dif', fieldName='factor')


def dif_gkmarin(database,datetime1,datetime2):

    df1 = Query_Data(database, 'M1004263',datetime1,datetime2)
    df2 = Query_Data(database, 'S0059744',datetime1,datetime2)
    df38 = pd.concat([df1, df2], axis=1)
    df38['yearmonth'] = df38.index.map(lambda x: 100 * x.year + x.month)
    df38 = df38.groupby('yearmonth').mean()
    df38['div']=df38['M1004263']/df38['S0059744']-1
    df38['factor']=df38['div'].diff()
    df38['Report_Date'] = df38.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df38['Release_Date'] = df38['Report_Date']
    print(df38)
    #return(df38,'GKBond_Tbond_Margin_Dif')
    Sava_Systmetic_Factor_To_Database(database, df38, savedName='GKBond_Tbond_Margin_Dif', fieldName='factor')


def dif_3amargin(database,datetime1,datetime2):

    df1 = Query_Data(database, 'S0059771',datetime1,datetime2)
    df2 = Query_Data(database, 'S0059744',datetime1,datetime2)
    df39 = pd.concat([df1, df2], axis=1)
    df39['yearmonth'] = df39.index.map(lambda x: 100 * x.year + x.month)
    df39 = df39.groupby('yearmonth').mean()
    df39['div'] = df39['S0059771'] / df39['S0059744'] - 1
    df39['factor'] = df39['div'].diff()
    df39['Report_Date'] = df39.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df39['Release_Date'] = df39['Report_Date']
    print(df39)
    #return(df39,'AAA_Tbond_Margin_Dif')
    Sava_Systmetic_Factor_To_Database(database, df39, savedName='AAA_Tbond_Margin_Dif', fieldName='factor')


def dif_7repo(database,datetime1,datetime2):

    df40 = Query_Data(database, 'M0041664',datetime1,datetime2)
    df40['yearmonth'] = df40.index.map(lambda x: 100 * x.year + x.month)
    df40 = df40.groupby('yearmonth').last()
    df40['factor'] = df40.diff()
    df40['Report_Date'] = df40.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    df40['Release_Date'] = df40['Report_Date']
    print(df40)
    #return(df40,'Repo007_Weighted_Monthly_Dif')
    Sava_Systmetic_Factor_To_Database(database, df40, savedName='Repo007_Weighted_Monthly_Dif', fieldName='factor')


def _7reposd(database,datetime1,datetime2):

   df41 = Query_Data(database,'M0041664',datetime1,datetime2)
   df41['factor']=df41.rolling(22,min_periods=2).std()
   df41['yearmonth']=df41.index.map(lambda x :100*x.year+x.month)
   df41=df41.groupby('yearmonth').last()
   df41['Report_Date'] = df41.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
   df41['Release_Date'] = df41['Report_Date']
   print(df41)
   #return(df41,'Repo007_Weighted_SD')
   Sava_Systmetic_Factor_To_Database(database, df41, savedName='Repo007_Weighted_SD', fieldName='factor')


def dif_chn_us_irs1y(database,datetime1,datetime2):

    df1 = Query_Data(database, 'S0059744',datetime1,datetime2)
    df2 = Query_Data(database, 'G0000886',datetime1,datetime2)
    df42 = pd.concat([df1, df2], axis=1)
    df42['yearmonth'] = df42.index.map(lambda x: 100 * x.year + x.month)
    df42 = df42.groupby('yearmonth').mean()
    df42['sub'] = df42['S0059744']-df42['G0000886']
    df42['factor'] = df42['sub'].diff()
    #df42['factor']= df42['factor'].where(df42['factor'].notnull(), None)
    df42['Report_Date'] = df42.index.astype(str).map(lambda x: x[0:4]+'-'+x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x : x + relativedelta(months=+1))
    df42['Release_Date'] = df42['Report_Date']
    print(df42)
    #return(df42,'dif_chn_us_irs1y')
    Sava_Systmetic_Factor_To_Database(database, df42, savedName='dif_chn_us_irs1y', fieldName='factor')



def Sava_Systmetic_Factor_To_Database(database, df, savedName, fieldName=""):
    Sava_Systmetic_To_Database(database, df, savedName, fieldName, tableName="a_sys_factor") # 向测试表中填写内容


def Sava_Systmetic_To_Database(database,
                               df,  # 要存入的Dataframe
                               savedName,  # 要以何种名字存在数据库中，对应数据表中“name”字段
                               fieldName="",   # 要存储Dataframe的哪列数据，对应dataframe的column名字
                               tableName=""):  # 要存入那张数据表，一般是 A_sys_raw 或者 A_sys_factor
    #
    if fieldName == "":
        fieldName = savedName
    #
    newDocuments = []
    for index, row in df.iterrows():
        value = row[fieldName]
        reportDate = row["Report_Date"]
        releaseDate = row["Release_Date"]
        #
        newDocument = {}
        newDocument["Name"] = savedName
        value = float(value)
        if np.isnan(value):
            value = None
        newDocument["Value"] = value
        newDocument["Report_Date"] = reportDate
        newDocument["Release_Date"] = releaseDate
        newDocument["Date"] = releaseDate
        newDocument["DateTime"] = releaseDate
        newDocument["Key2"] = savedName + "_" + Gadget.ToDateString(reportDate)
        newDocuments.append(newDocument)
    #
    database.Upsert_Many("Factor", tableName, [], newDocuments)
    #print("Saved Systematic Data", savedName, "#", len(newDocuments))

def test(database,datetime1,datetime2):

    funcs = []
    funcs.extend([h11001,slfmlfpsl,govt_leverage,difln_fxreserve,dif_hotmoney,rd,dif_rdd,lqdtysurplus,banksurplus,save_loan,tedsprd,
    difin_invstablecpt,dif_indav,dif_realestate,accfixinvs,cpimppi,difln_usdchn,exim,dif_liability,dif_profmargin,dif_att,
    newloan,newloan_resid_prop,newloan_shortprop,newloan_longprop,difln_multiplier,m0,m2mm1,timespread,creditspd10y,bondleverage,
    mmleverage,bond_stock,bond_future,difln_mkt_stkbd,dif_gkmarin,dif_3amargin,dif_7repo,_7reposd,dif_chn_us_irs1y])
    for func in funcs:
        func(database,datetime1,datetime2)
        # df['factor'].replace(np.nan, None, inplace=True)
        # fac_name = func(database)[1]
        # #print(df)
        # Sava_Systmetic_Factor_To_Database(database, df , savedName = fac_name , fieldName='factor')



if __name__ == '__main__':

    from Core.Config import *

    pathfilename = os.getcwd() + "\..\Config\config2.json"  # 确保config文件位于正确的位置
    config = Config(pathfilename)  # 实例化Config对象
    database = config.DataBase("JDMySQL")

    # 连接数据库
    # config = \
    #     {
    #         "financial_data": {"Username": "m_Financial_Data", "Password": "S1EbgH"},
    #         "derivative_data": {"Username": "m_Derivativepwba", "Password": "hq42YC"},
    #         "factor": {"Username": "m_Factor", "Password": "mOaruP"}
    #     }
    #
    # # 连接数据库
    # database = JDMySQLDB.JDMySQLDB("172.25.4.218", "3306", config=config)
    # #realtime = config.RealTime()  #
    datetime1=datetime.datetime(2019,1,1)
    datetime2=datetime.datetime(2020,5,1)
    # h11001(database,datetime1,datetime2)  #日数据取月末
    # #omo(database)         #有问题， #omo还没有录
    # slfmlfpsl(database,datetime1,datetime2)
    # govt_leverage(database,datetime1,datetime2)
    # difln_fxreserve(database,datetime1,datetime2)
    # dif_hotmoney(database,datetime1,datetime2)
    # rd(database,datetime1,datetime2)
    # dif_rdd(database,datetime1,datetime2)
    # lqdtysurplus(database,datetime1,datetime2)
    # banksurplus(database,datetime1,datetime2)
    # save_loan(database,datetime1,datetime2)
    # tedsprd(database,datetime1,datetime2)
    # difin_invstablecpt(database,datetime1,datetime2)
    # dif_indav(database,datetime1,datetime2)
    # dif_realestate(database,datetime1,datetime2)
    # accfixinvs(database,datetime1,datetime2)
    # cpimppi(database,datetime1,datetime2)
    # difln_usdchn(database,datetime1,datetime2) #日数据取月末
    # exim(database,datetime1,datetime2) #
    # dif_liability(database,datetime1,datetime2)#
    # dif_profmargin(database,datetime1,datetime2)
    # dif_att(database,datetime1,datetime2)
    # newloan(database,datetime1,datetime2)
    # newloan_resid_prop(database,datetime1,datetime2)
    # newloan_shortprop(database,datetime1,datetime2)
    # newloan_longprop(database,datetime1,datetime2)
    # difln_multiplier(database,datetime1,datetime2)
    # m0(database,datetime1,datetime2)
    # m2mm1(database,datetime1,datetime2)
    # timespread(database,datetime1,datetime2)
    # creditspd10y(database,datetime1,datetime2)
    # bondleverage(database,datetime1,datetime2)  #这个注意05年之前要拼接
    # mmleverage(database,datetime1,datetime2)
    # bond_stock(database,datetime1,datetime2)
    bond_future(database,datetime1,datetime2)   #新公式求，日度数据不求月平均，直接按月求和
    difln_mkt_stkbd(database,datetime1,datetime2)  #这个注意05年之前要拼接
    dif_gkmarin(database,datetime1,datetime2)
    dif_3amargin(database,datetime1,datetime2)
    dif_7repo(database,datetime1,datetime2)      #数值对不上 日数据取月末
    _7reposd(database,datetime1,datetime2)       #rolling 把时间补满
    dif_chn_us_irs1y(database,datetime1,datetime2)
    print('finish!')
    #test(database)



























